﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsScheduleDataAccess
    {
        public static DataTable SelectDialogList(string ScheduleID, string ProductID, string ProductName, string Specifications, string Unit, string OutSide, string InSide, int pageIndex, int pageSize, out int m_count)
        {   
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select o.*, ISNULL(o.ProduceNum,0)+ISNULL(o.LossNum,0)-ISNULL(cc.ScheduleTotalNum,0) as LastNum
                                        from V_PlanBySchedule o
                                        left join (select PlanID,SUM(ISNULL(ScheduleNum,0)) as ScheduleTotalNum 
                                                    from BS_ScheduleDetail where ScheduleID<>@ScheduleID group by PlanID) cc on cc.PlanID=o.PlanID 
                                        where ISNULL(o.ProduceNum,0)+ISNULL(o.LossNum,0)-ISNULL(cc.ScheduleTotalNum,0)>0";
                    dpc.AddWithValue("@ScheduleID", ScheduleID);
                    if (!string.IsNullOrEmpty(ProductID))
                    {
                        strcmd += " and o.ProductID like @ProductID";
                        dpc.AddWithValue("@ProductID", "%" + ProductID + "%");
                    }
                    if (!string.IsNullOrEmpty(ProductName))
                    {
                        strcmd += " and o.ProductName like @ProductName";
                        dpc.AddWithValue("@ProductName", "%" + ProductName + "%");
                    }
                    if (!string.IsNullOrEmpty(Specifications))
                    {
                        strcmd += " and o.Specifications like @Specifications";
                        dpc.AddWithValue("@Specifications", "%" + Specifications + "%");
                    }
                    if (!string.IsNullOrEmpty(Unit))
                    {
                        strcmd += " and o.Unit like @Unit";
                        dpc.AddWithValue("@Unit", "%" + Unit + "%");
                    }
                    if (!string.IsNullOrEmpty(OutSide))
                    {
                        strcmd += " and o.OutSide like @OutSide";
                        dpc.AddWithValue("@OutSide", "%" + OutSide + "%");
                    }
                    if (!string.IsNullOrEmpty(InSide))
                    {
                        strcmd += " and o.InSide like @InSide";
                        dpc.AddWithValue("@InSide", "%" + ProductName + "%");
                    }
                    strcmd += " order by o.PlanID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectList(string ScheduleID, string From, string End, int pageIndex, int pageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select s.* from V_ScheduleHead s where 1=1 ";
                    if (!string.IsNullOrEmpty(ScheduleID))
                    {
                        strcmd += " and s.ScheduleID like @ScheduleID";
                        dpc.AddWithValue("@ScheduleID", "%" + ScheduleID + "%");
                    }
                    if (!string.IsNullOrEmpty(From))
                    {
                        strcmd += " and s.BillDate >= @From";
                        dpc.AddWithValue("@From", From + " 00:00:00");
                    }
                    if (!string.IsNullOrEmpty(End))
                    {
                        strcmd += " and s.BillDate <= @End";
                        dpc.AddWithValue("@End", End + "23:59:59");
                    }
                    strcmd += " order by s.ScheduleID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetScheduleHeadByID(string ScheduleID)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select v.*,ISNULL(v.PlanNums,0)-ISNULL(cc.ScheduleTotalNum,0) as LastNum 
                                        from V_ScheduleHead v
                                        left join (select PlanID,SUM(ISNULL(ScheduleNum,0)) as ScheduleTotalNum 
			                                            from BS_ScheduleDetail where ScheduleID<>@ScheduleID group by PlanID ) cc on cc.PlanID=v.PlanID 
                                        where 1=1 ";
                    if (!string.IsNullOrEmpty(ScheduleID))
                    {
                        strcmd += " and v.ScheduleID = @ScheduleID";
                        dpc.AddWithValue("@ScheduleID", ScheduleID);
                    }
                    return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetScheduleBodyProcessByID(string ScheduleDetailID)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select sp.*,p.ProcessName 
                                    from BS_ScheduleProcess sp
	                                    left join MD_Process p on p.ProcessID=sp.ProcessID
	                                    where 1=1 ";
                    if (!string.IsNullOrEmpty(ScheduleDetailID))
                    {
                        strcmd += " and sp.ScheduleDetailID = @ScheduleDetailID";
                        dpc.AddWithValue("@ScheduleDetailID", ScheduleDetailID);
                    }
                    return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetMachineDataTable()
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    string strcmd = @"select LTRIM(RTRIM(MachineID)) as MachineID,MachineName from MD_Machine where [Status]='1'";
                    return DataAccessCommon.SelectDataSet(strcmd).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetBomDetailByWorkOrderNo(string WorkOrderNo)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select bd.*,m.MaterialName,m.Specifications,m.Unit,m.Long,m.Width,
                                        m.Height,m.OutSide,m.InSide
                                        from BS_BOM b
                                        left join BS_BOMDetail bd on bd.BOMID=b.BOMID
                                        left join MD_Material m on m.MaterialID=bd.MaterialID
                                        where b.WorkOrderNo=@WorkOrderNo and b.[Status]='1'";
                    dpc.AddWithValue("@WorkOrderNo", WorkOrderNo);
                    return DataAccessCommon.Select(strcmd,dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }
    }
}
